package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.swing.JOptionPane;

import constant.Constant;
import entity.Parameters;
import entity.TableNameAndType;

public class DBUtils {

	public static Connection getConnection(Parameters parameters) {
		Connection conn = null;

		String url = "";

		String driverClass = "";

		if ("mysql".equals(parameters.getDataBaseTypeVal())) {
			try {

				url = "jdbc:mysql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
						+ parameters.getDataBaseNameVal()
						+ "?connectTimeout=10000&socketTimeout=10000&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
				driverClass = "com.mysql.jdbc.Driver";

				Class.forName(driverClass);

				DriverManager.setLoginTimeout(10);

				conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
						parameters.getDataBasePwdVal());

			} catch (Exception e) {
				JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！", "错误",
						JOptionPane.ERROR_MESSAGE);
				return null;
			}
		} else if ("oracle".equals(parameters.getDataBaseTypeVal())) {

			try {

				url = "jdbc:oracle:thin:@" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + ":"
						+ parameters.getDataBaseNameVal();
				driverClass = "oracle.jdbc.driver.OracleDriver";

				Class.forName(driverClass);

				Properties info = new Properties();
				info.put("oracle.net.CONNECT_TIMEOUT", 10000);
				info.put("oracle.jdbc.ReadTimeout", 10000);
				info.put("user", parameters.getDataBaseUserNameVal());
				info.put("password", parameters.getDataBasePwdVal());
				DriverManager.setLoginTimeout(10);
				conn = DriverManager.getConnection(url, info);

			} catch (Exception e) {

				JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！", "错误",
						JOptionPane.ERROR_MESSAGE);
				return null;
			}

		}

		parameters.setDataBaseUrl(url);

		parameters.setDataBaseDriverClass(driverClass);

		parameters.setConnection(conn);

		return conn;
	}

	/**
	 * 仅限mysql
	 * 
	 * @param tableNameVal
	 * @param connection
	 * @return
	 */
	public static Map<String, String> getColumnComment(String tableNameVal, Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "show full columns from `" + tableNameVal + "`";

		// 列名集合
		Map<String, String> commentMqp = new HashMap<>();
		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();

			while (columnListRs.next()) {

				commentMqp.put(columnListRs.getString("Field").toUpperCase(), columnListRs.getString("Comment"));

			}

		} catch (SQLException e) {
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException e) {

			}
		}
		return commentMqp;
	}

	public static List<String> getColumnNameList(String databaseType, String tableNameVal, Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "";
		if ("mysql".equals(databaseType)) {
			sql = "select * from `" + tableNameVal + "` where 1=0";
		} else if ("oracle".equals(databaseType)) {
			sql = "select * from \"" + tableNameVal + "\" where 1=0";
		}

		// 列名集合
		List<String> columnList = new ArrayList<>();

		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();
			ResultSetMetaData metaData = columnListRs.getMetaData();

			int columnCount = metaData.getColumnCount();

			for (int i = 0; i < columnCount; i++) {
				columnList.add(metaData.getColumnName(i + 1));
			}

		} catch (SQLException e) {
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException e) {

			}
		}
		return columnList;
	}

	/**
	 * 获取表中所有字段名称和类型
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<TableNameAndType> getColumnNameAndTypes(String databaseType, String tableNameVal,
			Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "";
		if ("mysql".equals(databaseType)) {
			sql = "select * from `" + tableNameVal + "` where 1=0";
		} else if ("oracle".equals(databaseType)) {
			sql = "select * from \"" + tableNameVal + "\" where 1=0";
		}

		// 列名集合
		List<TableNameAndType> nameAndTypes = new ArrayList<>();

		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();
			ResultSetMetaData metaData = columnListRs.getMetaData();

			int columnCount = metaData.getColumnCount();

			for (int i = 0; i < columnCount; i++) {
				TableNameAndType nameAndType = new TableNameAndType();
				nameAndType.setName((metaData.getColumnName(i + 1)));
				nameAndType.setComment((metaData.getColumnName(i + 1)));

				String javaTypeName = "";
				String javaClassName = "";
				String columnTypeName = metaData.getColumnTypeName(i + 1).toUpperCase();

				switch (databaseType) {
				case "mysql":
					switch (columnTypeName) {
					case "VARCHAR":
						javaTypeName = "String";
						break;
					case "INT":
						javaTypeName = "Integer";
						break;
					case "CHAR":
						javaTypeName = "String";
						break;
					case "BLOB":
						javaTypeName = "byte[]";
						break;
					case "TEXT":
						javaTypeName = "String";
						break;
					case "INTEGER":
						javaTypeName = "Long";
						break;
					case "TINYINT":
						javaTypeName = "Integer";
						break;
					case "SMALLINT":
						javaTypeName = "Integer";
						break;
					case "MEDIUMINT":
						javaTypeName = "Integer";
						break;
					case "BIT":
						javaTypeName = "Integer";
						break;
					case "BIGINT":
						javaTypeName = "BigInteger";
						javaClassName = "java.math.BigInteger";
						break;
					case "FLOAT":
						javaTypeName = "Float";
						break;
					case "DOUBLE":
						javaTypeName = "Double";
						break;
					case "DECIMAL":
						javaTypeName = "BigDecimal";
						javaClassName = "java.math.BigDecimal";
						break;
					case "BOOLEAN":
						javaTypeName = "Integer";
						break;
					// 主键
					case "ID":
						javaTypeName = "Long";
						break;
					case "DATE":
						javaTypeName = "Date";
						javaClassName = "java.sql.Date";
						break;
					case "TIME":
						javaTypeName = "Time";
						javaClassName = "java.sql.Time";
						break;
					case "DATETIME":
						javaTypeName = "Timestamp";
						javaClassName = " java.sql.Timestamp";
						break;
					case "TIMESTAMP":
						javaTypeName = "Timestamp";
						javaClassName = " java.sql.Timestamp";
						break;
					case "YEAR":
						javaTypeName = "Date";
						javaClassName = "java.sql.Date";
						break;
					default:
						javaTypeName = "String";
						break;
					}
					break;
				case "oracle":
					switch (columnTypeName) {
					case "NUMBER":
						javaTypeName = "FLOAT";
					case "DATE":
						javaTypeName = "Date";
						javaClassName = "java.sql.Date";
						break;
					case "TIMESTAMP":
						javaTypeName = "Timestamp";
						javaClassName = " java.sql.Timestamp";
						break;
					default:
						javaTypeName = "String";
						break;
					}
				default:
					break;
				}

				nameAndType.setTypeName(javaTypeName);
				nameAndType.setClassName(javaClassName);

				nameAndTypes.add(nameAndType);
			}

			if ("mysql".equals(databaseType)) {

				// 注释map
				Map<String, String> columnComment = getColumnComment(tableNameVal, connection);

				for (TableNameAndType tableNameAndType : nameAndTypes) {

					String name = tableNameAndType.getName().toUpperCase();

					// 设置注释内容
					tableNameAndType.setComment(
							"".equals(columnComment.get(name)) ? tableNameAndType.getName() : columnComment.get(name));

				}
			}

		} catch (SQLException e) {
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException e) {

			}
		}
		return nameAndTypes;
	}

}
